<?php
require_once '../db/db.php';

$db = openDbConn();

function mpread( $xmlfile ) {
  // inspired by dom_to_simple_array( $domnode, &$array )
  $dom = new DomDocument;
  $dom->load( $xmlfile );
  $ret = array();
  mpnode( $dom, $ret );
  return $ret;
}
function mpreadTable( $xmlfile ) {
  // inspired by dom_to_simple_array( $domnode, &$array )
  $dom = new DomDocument;
  $dom->load( $xmlfile );
  $ret = array();
  mptable( $dom, $ret );
  return $ret;
}
function mpnode( $node, &$arr, $pos=0 ){
  $arrptr = &$arr;
  $node = $node->firstChild;
  $posOrg = $pos;
  while( !is_null($node) ) {

    if( $node->nodeType == XML_ELEMENT_NODE ) {
      $arrptr = &$arr[$node->nodeName];

      if( $node->hasAttributes() ) {
        $attr = $node->attributes;
        foreach( $attr as $data ) 
          $arrptr[$data->name] = $data->value;
      }

      if( $node->hasChildNodes() )  {
        $pos = mpnode( $node, $arrptr, $pos+1 );
      } else {
        $arrptr['lftn'] = ++$pos;
        $arrptr['rgtn'] = ++$pos;
      }
    }
     
    $node = $node->nextSibling;
  }
  $arr['lftn'] = $posOrg;
  return $arr['rgtn'] = $pos+1;
}
function mptable( $node, &$arr, $parent=".", $pos=0 ){
  $node = $node->firstChild;
  $posOrg = $pos;
  while( !is_null($node) ) {
    if( $node->nodeType == XML_ELEMENT_NODE ) {
      $arrptr = &$arr[];
      $arrptr[':name'] = $node->nodeName;
      $arrptr[':parent'] = $parent;

      if( $node->hasAttributes() ) {
        $attr = $node->attributes;
        foreach( $attr as $data ) {
          $arrptr[$data->name] = $data->value;
        }
      }

      $arrptr[':lftn'] = ++$pos;
      if( $node->hasChildNodes() )  {
        $pos = mptable( $node, $arr, $node->nodeName, $pos );
        $arrptr[':rgtn'] = $pos;
      } else {
        $arrptr[':rgtn'] = ++$pos;
      }
    }
     
    $node = $node->nextSibling;
  }
  return $pos+1;
}
//echo '<pre>';
//print_r( mpreadTable( './perm.xml' ) );
function insertMPTree( $db, $node, $parent = null ) {
  if( !$node ) 
    return;
  $db->exec( "INSERT INTO permtree (prgt, plft, parent, node) VALUES ('${node['rgtn']}', '${node['lftn']}', '$parent', '${node['val']}');" );
  insertMPTree( $db, $node['lft'], $node['val'] );
  insertMPTree( $db, $node['rgt'], $node['val'] );
}

try {
  $db->beginTransaction();


  /**
   * forum  ::
   *   id, title, datum, rank
   **/
  $db->exec( 'DROP TABLE IF EXISTS forum;' );
  $db->exec( 'CREATE TABLE forum 
                (id INTEGER PRIMARY KEY, 
                 title Text(200) NOT NULL, 
                 datum Text(200) NOT NULL, 
                 rank Integer NOT NULL);' );
  /**
   * thread ::
   *   id, fid, pid, prio, bumpdate, bumpuid
   **/
  $db->exec( 'DROP TABLE IF EXISTS thread;' );
  $db->exec( 'CREATE TABLE thread 
                (id INTEGER PRIMARY KEY, 
                 fid INTEGER NOT NULL, 
                 stream Integer NOT NULL,
                 prio Integer NOT NULL,
                 bumpdate Integer NOT NULL,
                 bumpuid Integer NOT NULL);' );
  /**
   * post   ::
   *   id, fid(NULL), tid, uid, title, rank, indent, date, smurfkey
   **/
  $db->exec( 'DROP TABLE IF EXISTS post;' );
  $db->exec( 'CREATE TABLE post 
                (id INTEGER PRIMARY KEY, 
                 title Text(200) NOT NULL, 
                 stream INTEGER NOT NULL, 
                 rank Integer NOT NULL,
                 indent Integer NOT NULL, 
                 datum CLOB NOT NULL,
                 date Integer NOT NULL, 
                 uid Integer NOT NULL,
                 smurfkey Text(500) NOT NULL);' );

  /**
   * user :: 
   *   name, pass, email, avatar, joindate, grpid, timezone, botcheck, rank, karma
   **/
  $db->exec( 'DROP TABLE IF EXISTS user;' );
  $db->exec( 'CREATE TABLE user 
                (id INTEGER PRIMARY KEY, 
                 name Text(200) UNIQUE NOT NULL, 
                 pass Text(250) NOT NULL, 
                 mail Text(200) UNIQUE NOT NULL,
                 joindate Integer NOT NULL,
                 permgrp Integer,
                 avatar Text(250));');
  /**
   * permgroup ::
   *   id, name
   **/
  $db->exec( 'DROP TABLE IF EXISTS permgroup;' ); 
  $db->exec( 'CREATE TABLE permgroup 
                (id INTEGER PRIMARY KEY,
                 name Text(250) NOT NULL );' );
  /**
   * permlist ::
   *   grpid, plft, prgt
   **/
  $db->exec( 'DROP TABLE IF EXISTS permlist;' );
  $db->exec( 'CREATE TABLE permlist 
                (grpid Integer NOT NULL,
                 id Integer NOT NULL,                 
                 lftn Integer NOT NULL,
                 rgtn Integer NOT NULL  );' );
  /**
   * permtree ::
   *   parent, node, lft, rgt
   **/
  $db->exec( 'DROP TABLE IF EXISTS permtree;' );
  $db->exec( 'CREATE TABLE permtree
                (parent Integer NOT NULL,
                 name Integer NOT NULL,
                 id Integer NOT NULL,
                 lftn Integer NOT NULL,
                 rgtn Integer NOT NULL );' );



  // // SAMPLE POPULATION // //

  $mptable = mpreadTable( './perm.xml' ); 
  $stmt = $db->prepare( "INSERT INTO permtree (name, id, lftn, rgtn, parent) VALUES (:name, :id, :lftn, :rgtn, :parent)" );
  foreach( $mptable as $record ) 
    $stmt->execute( $record );

  $db->exec( 'INSERT INTO permgroup (id, name) VALUES (1,"admin");' );
  $db->exec( 'INSERT INTO permlist (grpid, id, lftn, rgtn) VALUES (1, 1, (SELECT lftn FROM permtree WHERE id = 1), (SELECT rgtn FROM permtree WHERE id = 1));' );

  $db->exec( 'INSERT INTO permgroup (id, name) VALUES (2, "mod");' );
  $db->exec( 'INSERT INTO permgroup (id, name) VALUES (3, "user");' );
  $db->exec( 'INSERT INTO permgroup (id, name) VALUES (4, "guest");' );


  $grps = array( 2 => array(2,3,4400),
                 3 => array(2100,2210,3100,2400,3400,4410),
                 4 => array(4410,3400,2400) );

  foreach( $grps as $id => $perms ) {
    foreach($perms as $perm) 
      $db->exec( "INSERT INTO permlist (grpid, id, lftn, rgtn) VALUES (${id}, ${perm}, (SELECT lftn FROM permtree WHERE id = ${perm}), (SELECT rgtn FROM permtree WHERE id = ${perm}));" );
  }

  $db->exec("INSERT INTO user (id, name, pass, mail, permgrp, joindate, avatar) 
             VALUES (0,'guest','','nowhere5.com','4',".$db->quote(time()).", '../tmp/avatar/guest.png');");
  $db->exec("INSERT INTO user (id, name, pass, mail, permgrp, joindate, avatar) 
             VALUES (1,'root','".hash('sha512', 'root')."','nowhere4.com','1',".$db->quote(time()).", '../tmp/avatar/root.png');");
  $db->exec("INSERT INTO user (id, name, pass, mail, permgrp, joindate, avatar) 
             VALUES (2,'mod','".hash('sha512', 'mod')."','nowhere3.com','2',".$db->quote(time()).", '../tmp/avatar/mod.png');" );
  $db->exec("INSERT INTO user (id, name, pass, mail, permgrp, joindate, avatar) 
             VALUES (3,'user','".hash('sha512', 'user')."','nowhere2.com','3',".$db->quote(time()).", '../tmp/avatar/user.png');");


  $db->exec( 'INSERT INTO forum (id,title, datum, rank) VALUES(1,"f1","lots of love",1)');
  $db->exec( 'INSERT INTO post (id, uid, stream, title, rank, indent, date, smurfkey, datum) VALUES (1,1,1, "foo", 1, 0, 12, 34, "apple pie"); ' );
  $db->exec( 'INSERT INTO post (id, uid, stream, title, rank, indent, date, smurfkey, datum) VALUES (2,1,1, "foo2", 2, 0, 12, 34, "apple pie2"); ' );
  $db->exec( 'INSERT INTO post (id, uid, stream, title, rank, indent, date, smurfkey, datum) VALUES (3,1,2, "foo--", 1, 0, 12, 34, "apple pie--"); ' );
  $db->exec( 'INSERT INTO thread (fid, stream, prio, bumpdate, bumpuid) VALUES(1, 1, 0, 12, 0); ' );
  $db->exec( 'INSERT INTO thread (fid, stream, prio, bumpdate, bumpuid) VALUES(1, 2, 0, 12, 0); ' );

  $db->exec( 'INSERT INTO forum (id,title, datum, rank) VALUES(2,"f2","lots more love",1)');
  $db->exec( 'INSERT INTO post (id, uid, stream, title, rank, indent, date, smurfkey, datum) VALUES (4,1,3, "sexy", 1, 0, 12, 34, "i would do it"); ' );
  $db->exec( 'INSERT INTO thread (fid, stream, prio, bumpdate, bumpuid) VALUES(2, 3, 0, 12, 0); ' );


  $db->commit();
} catch ( Exception $e ) {
  $db->rollBack();
  die( $e->getMessage() );
}

echo 'DB BUILT';

?>
